    /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.NHANVIENDTO;
import SQL.DataProvider;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author NGUYEN
 */
public class NHANVIENDAO {

    
    public ArrayList<NHANVIENDTO> laynhanviencoloai(int loai)
    {
        DataProvider pro = new DataProvider();
        ResultSet rs = null;
        ArrayList<NHANVIENDTO> ds = new ArrayList<NHANVIENDTO>();
        try {
            pro.open();
            String query = "SELECT * FROM NhanVien WHERE MaLoaiNV = " + loai ;
            rs = pro.excutequery(query);
            while (rs.next()) {
                NHANVIENDTO dto = new NHANVIENDTO();
                int manv = Integer.parseInt(rs.getString("MaNV"));
                String password= rs.getString("Password");
                String tennv = rs.getString("TenNV");
                int maloainv = Integer.parseInt(rs.getString("MaLoaiNV"));
                String diachi=rs.getString("DiaChi");
                String phone=rs.getString("Phone");
                dto.setManv(manv);
                dto.setPassword(password);
                dto.setTennv(tennv);
                dto.setMaloainv(maloainv);
                dto.setDiachi(diachi);
                dto.setPhone(phone);
                ds.add(dto);
            }
            return ds;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return ds;
    }
    
    public ArrayList<NHANVIENDTO> laynhanviencoten(String ten)
    {
        DataProvider pro = new DataProvider();
        ResultSet rs = null;
        ArrayList<NHANVIENDTO> ds = new ArrayList<NHANVIENDTO>();
        try {
            pro.open();
            String query = "SELECT * FROM NhanVien WHERE TenNV LIKE '%" + ten + "%'";
            rs = pro.excutequery(query);
            while (rs.next()) {
                NHANVIENDTO dto = new NHANVIENDTO();
                int manv = Integer.parseInt(rs.getString("MaNV"));
                String password= rs.getString("Password");
                String tennv = rs.getString("TenNV");
                int maloainv = Integer.parseInt(rs.getString("MaLoaiNV"));
                String diachi=rs.getString("DiaChi");
                String phone=rs.getString("Phone");
                dto.setManv(manv);
                dto.setPassword(password);
                dto.setTennv(tennv);
                dto.setMaloainv(maloainv);
                dto.setDiachi(diachi);
                dto.setPhone(phone);
                ds.add(dto);
            }
            return ds;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return ds;
    }
    
    public NHANVIENDTO laynhanviencoma(int ma) {
        DataProvider pro = new DataProvider();
        ResultSet rs = null;
        NHANVIENDTO dto = new NHANVIENDTO();
        try {
            pro.open();
            String query = "SELECT * FROM NhanVien WHERE MaNV =" + ma;
            rs = pro.excutequery(query);
            if (rs.next()) {
                int manv = Integer.parseInt(rs.getString("MaNV"));
                String password= rs.getString("Password");
                String tennv = rs.getString("TenNV");
                int maloainv = Integer.parseInt(rs.getString("MaLoaiNV"));
                String diachi=rs.getString("DiaChi");
                String phone=rs.getString("Phone");
                dto.setManv(manv);
                dto.setPassword(password);
                dto.setTennv(tennv);
                dto.setMaloainv(maloainv);
                dto.setDiachi(diachi);
                dto.setPhone(phone);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return dto;
    }

    public boolean kiemtranhanvien(int ma, String pass) {
        DataProvider pro = new DataProvider();
        ResultSet rs = null;
        boolean kq = false;
        try {
            pro.open();
            String query = "SELECT * FROM NhanVien WHERE MaNV =" + ma + "AND Password = '" + pass + "'";
            rs = pro.excutequery(query);
            if (rs.next()) {
                kq = true;
            }
            return kq;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return kq;
    }

    public ArrayList<NHANVIENDTO> laydanhsachnhanvien() {
        DataProvider pro = new DataProvider();
        ArrayList<NHANVIENDTO> ds = new ArrayList<NHANVIENDTO>();
        ResultSet rs = null;
        try {
            pro.open();
            String query = "SELECT * FROM NHANVIEN";
            rs = pro.excutequery(query);
            while (rs.next()) {
                NHANVIENDTO dto = new NHANVIENDTO();
                dto.setManv(Integer.parseInt(rs.getString("MaNV")));
                dto.setPassword(rs.getString("Password"));
                dto.setTennv(rs.getString("TenNV"));
                dto.setMaloainv(Integer.parseInt(rs.getString("MaLoaiNV")));
                dto.setDiachi(rs.getString("DiaChi"));
                dto.setPhone(rs.getString("Phone"));
                ds.add(dto);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return ds;
    }

    public boolean themnhanvien(NHANVIENDTO dto) {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "INSERT INTO NhanVien(Password,TenNV,MaLoaiNV,DiaChi,Phone) VALUES('"
                    + dto.getPassword() + "',N'"
                    + dto.getTennv() + "',"
                    + dto.getMaloainv() + ",'"
                    + dto.getDiachi() + "','"
                    + dto.getPhone() + "')";
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
            throw e;
        }
        return false;
    }

    public boolean xoanhanvien(int ma) {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "DELETE FROM NhanVien WHERE MaNV = " + ma;
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean xoanhanvien(NHANVIENDTO dto) {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "DELETE FROM NhanVien WHERE MaNV = " + dto.getManv();
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean suanhanvien(NHANVIENDTO dto) {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "UPDATE NhanVien set "
                    + "Password='" + dto.getPassword()
                    + "',TenNV=N'" + dto.getTennv()
                    + "',MaLoaiNV='" + dto.getMaloainv()
                    + "',DiaChi='" + dto.getDiachi()
                    + "',Phone='" + dto.getPhone() + "' where MaNV= " + dto.getManv();
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
}
